/*
    RoleCall provides a web service that web applications can use to find 
    the roles associated with a user that has been authenticated via OpenID. 
    Also provides a web-based user interface to manage the user roles 
    associated with partner web applications.
    
    Development of this software was supported in part by the David and Lucile 
    Packard Foundation and by the the Office Of Naval Research (ONR) 
     
    Copyright (c) 2012, 
    Monterey Bay Aquarium Research Institute - MBARI ( www.mbari.org )
    Michael Godin ( mikegodin AT users DOT sourceforge DOT net )

    RoleCall is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    RoleCall is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Foobar (see the file COPYING.GPL).  If not, see 
    <http://www.gnu.org/licenses/>.  
*/

package org.mbari.rolecall.server;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.mbari.rolecall.model.Provider;
import org.mbari.rolecall.model.Relay;
import org.mbari.rolecall.model.RelayRole;
import org.mbari.rolecall.model.RelayUserRole;
import org.mbari.rolecall.model.Role;
import org.mbari.rolecall.model.Usage;
import org.mbari.rolecall.model.User;
import org.mbari.rolecall.model.UserRole;
import org.mbari.rolecall.model.UserRoleMatch;

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.jdbc.JdbcPooledConnectionSource;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.Where;
import com.j256.ormlite.table.TableUtils;

public class DB {

    static protected JdbcConnectionSource connectionSource = null;

    static private Dao<Provider, Integer> providerDao;

    static private Dao<Relay, Integer> relayDao;

    static private Dao<RelayRole, Integer> relayRoleDao;

    static private Dao<Role, Integer> roleDao;

    static private Dao<Usage, Integer> usageDao;

    static private Dao<User, Integer> userDao;

    static private Dao<UserRole, Integer> userRoleDao;

    static private Dao<UserRoleMatch, Integer> userRoleMatchDao;

    public static void addUserRoleMatchNamesToSet(String email, Relay relay,
            Set<String> userRoleNameSet) {
        List<String[]> results = rawQueryForRoleMatchNames(email, relay);
        if (null != results) {
            for (String[] result : results) {
                // the results array should have 1 value
                if (result.length > 0) {
                    userRoleNameSet.add(result[0]);
                }
            }
        }
        return;
    }

    public static void addUserRoleNamesToSet(User user, Relay relay,
            Set<String> userRoleNameSet) {
        List<UserRole> userRoleList = listUserRoles(user, relay);
        if (null != userRoleList) {
            for (UserRole userRole : userRoleList) {
                try {
                    Role role = userRole.getRole();
                    roleDao.refresh(role);
                    userRoleNameSet.add(role.getName());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static boolean deleteUserRole(UserRole userRole) {
        try {
            return userRoleDao.delete(userRole) == 1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static boolean deleteUserRoleMatch(UserRoleMatch userRoleMatch) {
        try {
            return userRoleMatchDao.delete(userRoleMatch) == 1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static Relay getRelay(Integer relayId, String remoteAddr) {
        Relay relay = null;
        try {
            relay = relayDao.queryForId(relayId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        if (null != relay && null != remoteAddr) {
            if (!relay.getIpAddress().equals(remoteAddr)) {
                return null;
            }
        }
        return relay;
    }

    public static Relay getRelay(String name, String ipAddress) {
        try {
            List<Relay> relayList = relayDao.queryBuilder().where()
                    .eq(Relay.FIELD_NAME_NAME, name).and()
                    .eq(Relay.FIELD_NAME_IP_ADDRESS, ipAddress).query();
            if (null != relayList & relayList.size() > 0) {
                return relayList.get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static RelayRole getRelayRole(Relay relay, Role role) {
        try {
            List<RelayRole> relayRoleList = relayRoleDao.queryBuilder().where()
                    .eq(RelayRole.FIELD_NAME_RELAY_ID, relay).and()
                    .eq(RelayRole.FIELD_NAME_ROLE_ID, role).query();
            if (null != relayRoleList && !relayRoleList.isEmpty()) {
                return relayRoleList.get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public static Role getRole(String roleName) {
        try {
            List<Role> roleList = roleDao.queryBuilder().where()
                    .eq(Role.FIELD_NAME_NAME, roleName).query();
            if (null != roleList && !roleList.isEmpty()) {
                return roleList.get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Usage getUsage(User user, Relay relay) {
        List<Usage> usageList = listUsage(user, relay);
        if (null != usageList && !usageList.isEmpty()) {
            return usageList.get(0);
        }
        return null;
    }

    public static User getUser(String email, String openId) {
        try {
            List<User> userList = userDao.queryBuilder().where()
                    .eq(User.FIELD_NAME_EMAIL, email).and()
                    .eq(User.FIELD_NAME_OPEN_ID, openId).query();
            if (null != userList && !userList.isEmpty()) {
                return userList.get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Object getUserRole(String email, String openId, Role role,
            Relay relay) {
        List<UserRole> userRoles = listUserRoles(email, openId, role, relay);
        if (null != userRoles && !userRoles.isEmpty()) {
            return userRoles.get(0);
        }
        return null;
    }

    public static UserRole getUserRole(User user, Role role) {
        List<UserRole> userRoles = listUserRoles(user, role);
        if (null != userRoles && !userRoles.isEmpty()) {
            return userRoles.get(0);
        }
        return null;
    }

    public static List<Provider> listProviders() {
        try {
            return providerDao.queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static List<RelayUserRole> listRelayUserRoles(User user) {
        if (null == user) {
            return null;
        }
        List<RelayUserRole> relayUserRoleList = new ArrayList<RelayUserRole>();
        List<UserRole> userRoleList = listUserRoles(user, (Relay) null);
        if (null != userRoleList) {
            RelayUserRole relayUserRole = null;
            for (UserRole userRole : userRoleList) {
                Relay relay = userRole.getRelay();
                if (relayUserRole == null
                        || relay.getId() != relayUserRole.getRelay().getId()) {
                    try {
                        relayDao.refresh(relay);
                        relayUserRole = new RelayUserRole(relay);
                        relayUserRoleList.add(relayUserRole);
                    } catch (SQLException e) {
                        e.printStackTrace();
                        continue;
                    }
                }
                relayUserRole.addUserRoleId(userRole.getRole().getId());
            }
        }
        List<String[]> rawIdList = rawQueryForRoleMatchIds(user.getEmail(),
                null);
        if (null != rawIdList) {
            for (String[] rawIds : rawIdList) {
                int roleId = Integer.parseInt(rawIds[0]);
                int relayId = Integer.parseInt(rawIds[1]);
                RelayUserRole relayUserRole = null;
                for (RelayUserRole aRelayUserRole : relayUserRoleList) {
                    if (aRelayUserRole.getRelay().getId() == relayId) {
                        relayUserRole = aRelayUserRole;
                        break;
                    }
                }
                if (relayUserRole == null) {
                    Relay relay = getRelay(relayId, null);
                    if (null == relay) {
                        continue;
                    }
                    relayUserRole = new RelayUserRole(relay);
                    relayUserRoleList.add(relayUserRole);
                }
                relayUserRole.addUserRoleId(roleId);
            }
        }
        return relayUserRoleList;
    }

    public static List<User> listRelayUsers(Relay relay) {
        List<User> users = null;
        List<Usage> usages = DB.listUsage(null, relay);
        if (null != usages && !usages.isEmpty()) {
            users = new ArrayList<User>(usages.size());
            for (Usage usage : usages) {
                User user = usage.getUser();
                try {
                    userDao.refresh(user);
                    users.add(user);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return users;
    }

    public static List<Role> listRoles() {
        try {
            return roleDao.queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static List<Usage> listUsage(User user, Relay relay) {
        List<Usage> usageList = null;
        try {
            Where<Usage, Integer> where = usageDao.queryBuilder().where();
            if (null != user) {
                where = where.eq(Usage.FIELD_NAME_USER_ID, user);
            }
            if (null != relay) {
                if (null != user) {
                    where = where.and();
                }
                where = where.eq(Usage.FIELD_NAME_RELAY_ID, relay);
            }
            usageList = where.query();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return usageList;
    }

    public static List<UserRoleMatch> listUserRoleMatches(Relay relay) {
        List<UserRoleMatch> userRoleMatchList = null;
        if (null == relay) {
            return userRoleMatchList;
        }
        try {
            QueryBuilder<UserRoleMatch, Integer> queryBuilder = userRoleMatchDao
                    .queryBuilder();
            queryBuilder.where().eq(UserRoleMatch.FIELD_NAME_RELAY_ID, relay);
            queryBuilder.orderBy(UserRoleMatch.FIELD_NAME_EMAIL_PATTERN, true);
            userRoleMatchList = queryBuilder.query();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userRoleMatchList;

    }

    public static List<UserRole> listUserRoles(String email, String openId,
            Role role, Relay relay) {
        List<UserRole> userRoleList = null;
        if (null == email || null == role || null == relay) {
            return userRoleList;
        }
        try {
            QueryBuilder<UserRole, Integer> queryBuilder = userRoleDao
                    .queryBuilder();
            Where<UserRole, Integer> where = queryBuilder.where();
            Where<UserRole, Integer> whereStatement = where
                    .eq(UserRole.FIELD_NAME_EMAIL, email).and()
                    .eq(UserRole.FIELD_NAME_ROLE_ID, role).and()
                    .eq(UserRole.FIELD_NAME_RELAY_ID, relay);
            if (null == openId) {
                whereStatement = whereStatement.and().isNull(
                        UserRole.FIELD_NAME_OPEN_ID);
            } else {
                whereStatement = whereStatement.and().eq(
                        UserRole.FIELD_NAME_OPEN_ID, openId);
            }
            userRoleList = queryBuilder.query();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userRoleList;
    }

    @SuppressWarnings("unchecked")
    public static List<UserRole> listUserRoles(User user, Relay relay) {
        List<UserRole> userRoleList = null;
        if (null == user && null == relay) {
            return userRoleList;
        }
        try {
            QueryBuilder<UserRole, Integer> queryBuilder = userRoleDao
                    .queryBuilder();
            Where<UserRole, Integer> where = queryBuilder.where();
            Where<UserRole, Integer> whereStatement = where;
            if (null != user) {
                whereStatement = where.and(
                        where.eq(UserRole.FIELD_NAME_EMAIL, user.getEmail()),
                        where.or(
                                where.eq(UserRole.FIELD_NAME_OPEN_ID,
                                        user.getOpenId()),
                                where.isNull(UserRole.FIELD_NAME_OPEN_ID)));
            }
            if (null != relay) {
                if (null != user) {
                    whereStatement = whereStatement.and().eq(
                            UserRole.FIELD_NAME_RELAY_ID, relay);
                } else {
                    whereStatement = where.eq(UserRole.FIELD_NAME_RELAY_ID,
                            relay);
                }
            } else {
                queryBuilder.orderBy(UserRole.FIELD_NAME_RELAY_ID, true);
            }
            userRoleList = queryBuilder.query();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userRoleList;
    }

    @SuppressWarnings("unchecked")
    public static List<UserRole> listUserRoles(User user, Role role) {
        List<UserRole> userRoleList = null;
        try {
            QueryBuilder<UserRole, Integer> queryBuilder = userRoleDao
                    .queryBuilder();
            Where<UserRole, Integer> where = queryBuilder.where();
            Where<UserRole, Integer> whereStatement = where;
            if (null != user) {
                whereStatement = where
                        .eq(UserRole.FIELD_NAME_EMAIL, user.getEmail())
                        .and()
                        .or(where.eq(UserRole.FIELD_NAME_OPEN_ID,
                                user.getOpenId()),
                                where.isNull(UserRole.FIELD_NAME_OPEN_ID));
            }
            if (null != role) {
                if (null != user) {
                    whereStatement = whereStatement.and().eq(
                            UserRole.FIELD_NAME_ROLE_ID, role);
                } else {
                    whereStatement = where
                            .eq(UserRole.FIELD_NAME_ROLE_ID, role);
                }
            } else {
                queryBuilder.orderBy(UserRole.FIELD_NAME_ROLE_ID, true);
            }
            userRoleList = queryBuilder.query();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userRoleList;
    }

    public static boolean persist(Relay relay) {
        try {
            if (0 == relay.getId()) {
                relayDao.create(relay);
            } else {
                relayDao.update(relay);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static boolean persist(RelayRole relayRole) {
        try {
            if (0 == relayRole.getId()) {
                relayRoleDao.create(relayRole);
            } else {
                relayRoleDao.update(relayRole);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static boolean persist(Role role) {
        try {
            if (0 == role.getId()) {
                roleDao.create(role);
            } else {
                roleDao.update(role);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static boolean persist(Usage usage) {
        try {
            if (0 == usage.getId()) {
                usageDao.create(usage);
            } else {
                usageDao.update(usage);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static boolean persist(User user) {
        try {
            if (0 == user.getId()) {
                userDao.create(user);
            } else {
                userDao.update(user);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static boolean persist(UserRole userRole) {
        try {
            if (0 == userRole.getId()) {
                userRoleDao.create(userRole);
            } else {
                userRoleDao.update(userRole);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static boolean persist(UserRoleMatch userRoleMatch) {
        try {
            if (0 == userRoleMatch.getId()) {
                userRoleMatchDao.create(userRoleMatch);
            } else {
                userRoleMatchDao.update(userRoleMatch);
            }
        } catch (SQLException sqle) {
            return false;
        }
        return true;
    }

    public static List<String[]> rawQueryForRoleMatchIds(String email,
            Relay relay) {
        List<String[]> results = null;
        try {
            StringBuilder queryStr = new StringBuilder("SELECT ")
                    .append(UserRoleMatch.FIELD_NAME_ROLE_ID).append(",")
                    .append(UserRoleMatch.FIELD_NAME_RELAY_ID).append(" FROM ")
                    .append(UserRoleMatch.TABLE_NAME).append(" WHERE '")
                    .append(email).append("' LIKE ")
                    .append(UserRoleMatch.FIELD_NAME_EMAIL_PATTERN);
            if (null != relay) {
                queryStr.append(" AND ")
                        .append(UserRoleMatch.FIELD_NAME_RELAY_ID)
                        .append(" = ").append(relay.getId());
            } else {
                queryStr.append(" ORDER BY ")
                        .append(UserRoleMatch.FIELD_NAME_RELAY_ID)
                        .append(" ASC");
            }

            GenericRawResults<String[]> rawResults = userRoleMatchDao
                    .queryRaw(queryStr.toString());
            results = rawResults.getResults();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return results;
    }

    public static List<String[]> rawQueryForRoleMatchNames(String email,
            Relay relay) {
        List<String[]> results = null;
        try {
            StringBuilder queryStr = new StringBuilder("SELECT ")
                    .append(Role.FIELD_NAME_NAME).append(" FROM ")
                    .append(Role.TABLE_NAME).append(" JOIN ")
                    .append(UserRoleMatch.TABLE_NAME).append(" ON ")
                    .append(Role.TABLE_NAME).append(".id=")
                    .append(UserRoleMatch.FIELD_NAME_ROLE_ID)
                    .append(" WHERE '").append(email).append("' LIKE ")
                    .append(UserRoleMatch.FIELD_NAME_EMAIL_PATTERN);
            if (null != relay) {
                queryStr.append(" AND ")
                        .append(UserRoleMatch.FIELD_NAME_RELAY_ID)
                        .append(" = ").append(relay.getId());
            }

            GenericRawResults<String[]> rawResults = userRoleMatchDao
                    .queryRaw(queryStr.toString());
            results = rawResults.getResults();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return results;
    }

    /**
     * Setup our database and DAOs
     */
    public static void setup() {
        try {
            connectionSource = new JdbcPooledConnectionSource(
                    Props.getConnectionURL(), Props.getConnectionUserName(),
                    Props.getConnectionPassword());
            ((JdbcPooledConnectionSource) connectionSource)
                    .setTestBeforeGet(true);

            // Create Data Access Objects

            relayRoleDao = DaoManager.createDao(connectionSource,
                    RelayRole.class);
            userRoleDao = DaoManager
                    .createDao(connectionSource, UserRole.class);
            userRoleMatchDao = DaoManager.createDao(connectionSource,
                    UserRoleMatch.class);
            providerDao = DaoManager
                    .createDao(connectionSource, Provider.class);
            relayDao = DaoManager.createDao(connectionSource, Relay.class);
            roleDao = DaoManager.createDao(connectionSource, Role.class);
            usageDao = DaoManager.createDao(connectionSource, Usage.class);
            userDao = DaoManager.createDao(connectionSource, User.class);

            // Create Tables (if needed)

            TableUtils
                    .createTableIfNotExists(connectionSource, RelayRole.class);
            TableUtils.createTableIfNotExists(connectionSource, UserRole.class);
            TableUtils.createTableIfNotExists(connectionSource,
                    UserRoleMatch.class);
            TableUtils.createTableIfNotExists(connectionSource, Provider.class);
            TableUtils.createTableIfNotExists(connectionSource, Relay.class);
            TableUtils.createTableIfNotExists(connectionSource, Role.class);
            TableUtils.createTableIfNotExists(connectionSource, Usage.class);
            TableUtils.createTableIfNotExists(connectionSource, User.class);

            // Update tables from earlier versions as necessary
            
            
            // Populate Provider table if it is empty

            if (providerDao.countOf() < 2) {
                Provider googleProvider = new Provider("Google",
                        "http://www.google.com/favicon.ico",
                        "https://www.google.com/accounts/o8/id", null);
                providerDao.create(googleProvider);
                Provider yahooProvider = new Provider("Yahoo",
                        "http://www.yahoo.com/favicon.ico",
                        "https://me.yahoo.com/", null);
                providerDao.create(yahooProvider);
            }
            //if (providerDao.countOf() < 3) {
            //    Provider mbariProvider = new Provider("MBARI",
            //            "http://www.mbari.org/favicon.ico",
            //            "https://crowd.mbari.org:8443/openidserver/users/%s",
            //            "Enter your MBARI id");
            //    providerDao.create(mbariProvider);
            //}

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void teardown() {
        if (null != connectionSource) {
            try {
                connectionSource.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
